package com.fhc.excel


import android.content.Context
import android.graphics.Bitmap
import android.os.Environment
import org.apache.poi.ss.usermodel.CellType
import org.apache.poi.ss.usermodel.Sheet
import org.apache.poi.ss.usermodel.Workbook
import org.apache.poi.ss.usermodel.WorkbookFactory
import org.apache.poi.xssf.usermodel.XSSFWorkbook
import java.io.ByteArrayOutputStream
import java.io.File
import java.io.FileInputStream
import java.io.FileOutputStream
import java.io.IOException
import android.graphics.BitmapFactory
import org.apache.poi.ss.usermodel.ClientAnchor
import kotlin.math.max
import androidx.core.graphics.scale
import timber.log.Timber
import kotlin.math.abs
import kotlin.math.min

data class User(
    val name: String = "",
    val address: String = ""
)

fun createExcel(context: Context, imagePath: String) {
    val data: MutableList<User> = mutableListOf<User>(User("11","22"),User("AA","BB"),User("**",",,"))
    val fileName = "AAAA.xlsx"
    val file = File(context.filesDir, fileName).apply {
        parentFile?.mkdirs()
    }
    val workbook = XSSFWorkbook()
//    在 Excel 中，行高和列宽使用不同的计量单位：
//
//    ‌列宽单位‌：以 1/256 字符宽度为单位
//    例如：setColumnWidth(5, 30 * 256) 表示将第 5 列设置为 30 个字符宽度
//    ‌行高单位‌：以点（point）为单位（1 点 ≈ 1/72 英寸）
//    例如：setDefaultRowHeightInPoints(30f) 设置默认行高为 30 点
    val sheet = workbook.createSheet("Hoja 1").apply {
//        // 统一使用字符单位设置列宽
//        setColumnWidth(2, (20 * 256)) // 设置第2列宽度为20字符
//
//        // 统一行高设置为点单位
//        setDefaultRowHeightInPoints(20f) // 20点 ≈ 15像素
    }


    val headerRow = sheet.createRow(0)
    var cell = headerRow.createCell(0)
    cell.setCellValue("name")

    cell = headerRow.createCell(1)
    cell.setCellValue("address")

    for (index in data.indices) {
        val row = sheet.createRow(index + 1)
        row.createCell(0).setCellValue(data[index].name)
        row.createCell(1).setCellValue(data[index].address)
    }
    BitmapFactory.decodeFile(imagePath)?.let { origBitmap ->
        val maxDisplayWidth = 800f
        val scaleRatio = maxDisplayWidth / origBitmap.width
        val scaledBitmap = origBitmap.scale(maxDisplayWidth.toInt(), (origBitmap.height * scaleRatio).toInt()).takeIf {
            val origRatio = origBitmap.width.toFloat() / origBitmap.height
            val newRatio = it.width.toFloat() / it.height
            abs(origRatio - newRatio) / origRatio < 0.02f
        } ?: return@let

        ByteArrayOutputStream().use { baos ->
            scaledBitmap.compress(Bitmap.CompressFormat.PNG, 85, baos)
            val pictureIdx = workbook.addPicture(baos.toByteArray(), Workbook.PICTURE_TYPE_PNG)

            val anchor = workbook.creationHelper.createClientAnchor().apply {
                setCol1(2) // 起始列
                row1 = 5   // 起始行

                //这里是根据excel默认列宽，行高
                val pixelPerCol = 104
                val pixelPerRow = 28

                val colSpan = (scaledBitmap.width / pixelPerCol)
                val rowSpan = colSpan * (pixelPerCol/pixelPerRow)

                setCol2(2 + colSpan)
                row2 = 5 + rowSpan
                anchorType = ClientAnchor.AnchorType.MOVE_AND_RESIZE
            }
            sheet.createDrawingPatriarch().createPicture(anchor, pictureIdx)
        }
    }

    try {
        val fileOutputStream = FileOutputStream( file )
        workbook.write(fileOutputStream)
        fileOutputStream.close()
        workbook.close()
    } catch (e: IOException) {
        e.printStackTrace()
    }

}

fun leerExcel(data: MutableList<User>) {
    val fileName = "registros.xlsx"

    val path = Environment.getExternalStoragePublicDirectory(Environment.DIRECTORY_DOWNLOADS).absolutePath+"/"+fileName


    val lista = arrayListOf<String>()

    try {
        val fileInputStream = FileInputStream(path)
        val workbook = WorkbookFactory.create(fileInputStream)
        val sheet: Sheet = workbook.getSheetAt(0)

        val rows = sheet.iterator()
        while (rows.hasNext()) {
            val currentRow = rows.next()

            // Iterar sobre celdas de la fila actual
            val cellsInRow = currentRow.iterator()
            while (cellsInRow.hasNext()) {
                val currentCell = cellsInRow.next()

                // Obtener valor de la celda como String
                val cellValue: String = when (currentCell.cellType) {
                    CellType.STRING -> currentCell.stringCellValue
                    CellType.NUMERIC -> currentCell.numericCellValue.toString()
                    CellType.BOOLEAN -> currentCell.booleanCellValue.toString()
                    else -> ""
                }

                lista.add(cellValue)

                //Log.d("ExcelReader", "Valor de celda: $cellValue")
            }
        }

        for (i in 2 until lista.size step 2) {
            data.add(
                User(
                    lista[i],
                    lista[i+1]
                )
            )
        }

        workbook.close()
        fileInputStream.close()
    } catch (e: IOException) {
        e.printStackTrace()
    }
}